﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace 学生选课及成绩查询管理系统
{
    public partial class optionalCourseToChooseInfo : Form
    {
        public optionalCourseToChooseInfo()
        {
            InitializeComponent();
        }
        User user;

        internal User User
        {
            get { return user; }
            set { user = value; }
        }
        
        
        private void optionalCourseToChooseInfo_Load(object sender, EventArgs e)
        {
            SqlParameter[] pars = { };
            DataTable dt = DBAccess.RunProcedure("proc_AllOptionCurse", pars).Tables[0];
            dataGridView1.DataSource = dt;
            //string sql = "select c.cno as '课程ID',c.cname as '课程名',c.tno as '教师号',t.tname as '教师名',credit as '学分',cproperties as  '课程性质',cCount as '剩余' from course c join teacher t on c.tno=t.tno where cproperties='选修'";
            //DataTable dt=DBAccess.GetTable(sql);
            //this.dataGridView1.DataSource = dt;

            txt_stuid.Text=user.Username;
            string my_sql = "select c.cno as '课程ID',c.cname as '课程名',credit as '学分',cproperties as  '课程性质' from course c join teacher t on c.tno=t.tno join grade g on g.cno=c.cno  where cproperties='选修' and status=1  and g.stuno=@stuno" ;
            SqlParameter[] sqlarray = {new SqlParameter("@stuno",txt_stuid.Text)};
            DataTable my_dt = DBAccess.GetTable(my_sql,sqlarray);
            this.dataGridView2.DataSource = my_dt;
            //SqlParameter[] pars2 = { new SqlParameter("@stuno", txt_stuid.Text), new SqlParameter("@cno",txt_delCid.Text) };
            //string my_sql = "select c.cno as '课程ID',c.cname as '课程名',credit as '学分',cproperties as  '课程性质' from course c join teacher t on c.tno=t.tno join grade g on g.cno=c.cno  where cproperties='选修' and g.stuno=@stuno";
            //DataTable dt2=DBAccess.RunProcedure("pro_myoptionCourse",pars2).Tables[0];
            //dataGridView2.DataSource=dt2;
       
        }

        private void txt_stuid_TextChanged(object sender, EventArgs e)
        {

        }

        private void button2_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void button4_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void listBox1_SelectedIndexChanged(object sender, EventArgs e)
        {

        }
        private void button1_Click(object sender, EventArgs e)
        {
            string cno = txt_cid.Text;
            string cname = txt_cname.Text;
            string stuno = user.Username;
            //string sql_judge = "select * from course where cproperties='选修'and cno=" + cno;
            string sql_judge = "select * from course where cproperties='选修'and cno=@cno";
            SqlParameter[] pars = { new SqlParameter("@cno",cno) };
            if (cname==""||stuno=="")
            {
                MessageBox.Show("请输入完整信息！");
                return;
            }
            SqlDataReader sdr = DBAccess.GetReader(sql_judge,pars);
            if (sdr.Read() == true)
            {
                if (sdr["cCount"].ToString() == "0")
                {
                    MessageBox.Show("剩余已为0,请选择其他课程！");
                    return;
                }
            }
            else
            {
                MessageBox.Show("剩余已为0,请选择其他课程！");
                return;
            }
            string sql_select = "select count(*) from grade where status=1 and stuno=@stuno and cno=@cno";
            SqlParameter[] SqlPArry = { new SqlParameter("@stuno", stuno),
                                          new SqlParameter("@cno", cno) };

            object count = DBAccess.GetOne(sql_select, SqlPArry);
            if ((int)count!=0)
            {
                MessageBox.Show("这门课程你已经选了！");
                return;
            }
            string sql2 = "select COUNT(*) from course where cno=@cno and cname=@cname";
            SqlParameter[] sqlarray = { new SqlParameter("@cno", cno), new SqlParameter("@cname", cname) };
            object res2 = DBAccess.GetOne(sql2, sqlarray);
            if ((int)res2 == 0)
            {
                MessageBox.Show("课程id 对应课程名不正确,请重新填写");
                return;
            }


            string sql_count = "select count(*) from grade g join course c on g.cno=c.cno where cproperties='选修' and status=1  and g.stuno=@stuno";
            SqlParameter[] sqlarray3 = { new SqlParameter("@stuno", stuno) };
            object cCount = DBAccess.GetOne(sql_count,sqlarray3);
            if ((int)cCount==2)
            {
                MessageBox.Show("只能同时选修两门课，可尝试删除原选修课程，再进行选择！");
                return;
            }
            //string sql = "insert into grade(stuno,cno) values(@stuno,@cno)";
            int res = DBAccess.RunProcedureExecuteNonQuery("pro_jugdeCourseUpdate",SqlPArry);
            //int res = DBAccess.GetState(sql, SqlPArry);
            if (res!=0)
            {
                MessageBox.Show("选课成功");
                string sql3 = "update course set cCount=cCount-1 where cproperties='选修'and cno="+cno;
                DBAccess.GetState(sql3);
                optionalCourseToChooseInfo_Load(sender, e);
            }
            else
            {
                MessageBox.Show("选课失败");
                return;
            }

        }

        private void txt_cname_TextChanged(object sender, EventArgs e)
        {
            
        }
        private void button1_MouseLeave(object sender, EventArgs e)
        {
                     
        }

        private void button3_Click(object sender, EventArgs e)
        {
            //listBox1.Items.Remove(listBox1.SelectedItem); 
            //string sql = "";
        }

        private void txt_cname_Click(object sender, EventArgs e)
        {
            string cid = txt_cid.Text;
            if (cid=="")
            {
                return;
            }
            string sql = "select COUNT(*) from course where " + cid + " in (select cno from course) and cproperties='选修'";
            object res = DBAccess.GetOne(sql);
            if ((int)res == 0)
            {
                MessageBox.Show("该不在选修中！请核对后再填写");
                txt_cid.Focus();
                return;
            }
        }

        private void txt_cname_Leave(object sender, EventArgs e)
        {
            
        }

        private void button5_Click(object sender, EventArgs e)
        {
            //string cid = txt_cid.Text;
            //string cname = txt_cname.Text;
            //string sql = "select COUNT(*) from course where cno=@cno and cname=@cname";
            //SqlParameter[] sqlarray = { new SqlParameter("@cno", cid), new SqlParameter("@cname", cname) };
            //object res = DBAccess.GetOne(sql, sqlarray);
            //if ((int)res == 0)
            //{
            //    MessageBox.Show("课程id 对应课程名不正确,请重新填写");
            //    return;
            //}
            //else
            //{
            //    MessageBox.Show("信息无误");
            //} 
        }

        private void btn_delById_Click(object sender, EventArgs e)
        {
            string cno = txt_delCid.Text;
            string sql = "update grade set status=0 where cno=@cno and stuno=@stuno";
            SqlParameter[] sqlarray = { new SqlParameter("@cno",cno),new SqlParameter("@stuno",user.Username) };
            int res = DBAccess.GetState(sql,sqlarray);
            //int res=DBAccess.RunProcedureExecuteNonQuery("pro_jugdeCourseDel",sqlarray);
            if (res!=0)
            {
                MessageBox.Show("删除成功！");
                string sql3 = "update course set cCount=cCount+1 where cproperties='选修' and cno= "+cno;
                DBAccess.GetState(sql3);
                optionalCourseToChooseInfo_Load(sender,e);
            }
            else
            {
                MessageBox.Show("删除失败！");
                return;
            }

        }

        private void dataGridView1_Click(object sender, EventArgs e)
        {

        }

        private void dataGridView1_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            
            //for (int i = 0; i < dataGridView1.Rows.Count; i++)
            //{
            //    for (int j = 1; j <= 6; j++)
            //    {
            //        string colo = this.dataGridView1.Columns[this.dataGridView1.Rows[i].Cells[j].ColumnIndex].HeaderText;
            //        txt_cid.Text=dataGridView1.Rows[i].Cells[0].Value.ToString();
            //        txt_cname.Text=dataGridView1.Rows[i].Cells[j].Value.ToString();
            //    }
            //}
            int row = dataGridView1.CurrentCellAddress.Y + 1;
            int col = dataGridView1.CurrentCellAddress.X + 1;
            txt_cid.Text= dataGridView1.Rows[row - 1].Cells[0].Value.ToString();
            txt_cname.Text = dataGridView1.Rows[row - 1].Cells[1].Value.ToString();
            
        }

        private void dataGridView2_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            int row = dataGridView2.CurrentCellAddress.Y + 1;
            int col = dataGridView2.CurrentCellAddress.X + 1;
            txt_delCid.Text = dataGridView2.Rows[row - 1].Cells[0].Value.ToString();
        }
    }
}
